package ar.edu.utn.frgp.servicio;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import ar.edu.utn.frgp.model.Paquete;

import com.mysql.jdbc.Connection;

public class PaqueteService {

	public List<Paquete> getPaquetes() throws Exception{
		List<Paquete> paq = new ArrayList<Paquete>();
		
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			
			try{
				java.sql.Statement st = conn.createStatement();	
				ResultSet rs = st.executeQuery("SELECT idPaquete, ciudadaDestino, ciudadOrigen, aeroOrigen, aeroDestino, fechaDesde, fechaHasta, avion, localidadesDisp, precio  FROM paquetes where localidadesDisp > 0 ");
				while(rs.next()){
					paq.add(new Paquete(rs.getInt("idPaquete"), rs.getString("ciudadOrigen"), rs.getString("ciudadaDestino"), rs.getString("aeroOrigen"), rs.getString("aeroDestino"), rs.getString("fechaDesde"), rs.getString("fechaHasta"), rs.getString("avion"), rs.getString("localidadesDisp"),  rs.getString("precio") ));
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		} catch (Exception e) {
			throw new Exception(e.toString());
		}
		
		return paq;
	}
	
	public void acualizarDisponibles(String idPaquete, String operador){
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		
		try{
			Class.forName("com.mysql.jdbc.Driver");				
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			java.sql.Statement st = conn.createStatement();
		if (operador == "+") st.executeUpdate("UPDATE paquetes set localidadesDisp = (localidadesDisp + 1 ) where  idPaquete = '" + idPaquete + "' ");
			else st.executeUpdate("UPDATE paquetes set localidadesDisp = (localidadesDisp - 1 ) where  idPaquete = '" + idPaquete + "' ");
		
		}catch(Exception e){
			e.printStackTrace();
		}	
	}
	
	public int getLocalidadesDispnibles(String idPaquete){
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		int local = 0;
		try{
			Class.forName("com.mysql.jdbc.Driver");				
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			java.sql.Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT localidadesDisp FROM paquetes where idPaquete = '" + idPaquete + "' ");
			while(rs.next()){
				local = rs.getInt("localidadesDisp");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return local;
		
	}
	
	
}
